package ctm.javacode;

import java.io.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.NumberFormat;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

@WebServlet("/maintenanceNourishment.do")
public class maintenanceNourishment extends HttpServlet {
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		/****** 設定 ******/
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		dbBean db = new dbBean();
		db.setDataSource("jdbc/ctmpllab");
		db.Connected();
		PrintWriter out = response.getWriter();// Write the response back to the
												// browser

		String type = request.getParameter("type");
		String company = request.getParameter("company");
		String productname = request.getParameter("productname");
		String powder = "";
		String water = "";
		String kcal = "";
		String protein = "";
		String fat = "";
		String carbohydrate = "";
		String fiber = "";
		String ca = "";
		String p = "";
		String na = "";
		String k = "";
		String mg = "";
		String g_spoon = "";

		/****** 資料庫變數 ******/
		String sqlQuery = "";
		ResultSet rss;

		switch (type) {
		case "getproductname":
			/****** 設定 ******/
			out.print("<option>");
			out.print("請選擇");
			out.print("</option>");

			/****** 資料庫抓取資料 ******/
			sqlQuery = "SELECT * FROM `nourishment` WHERE company =" + "'"
					+ company + "'";

			try {
				rss = db.ExcuteQuery(sqlQuery);
				while (rss.next()) {
					out.print("<option value='" + rss.getString("productname")
							+ "'>");
					out.print(rss.getString("productname"));
					out.print("</option>");
				}
				db.CloseConn();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			break;

		case "getdata":
			/****** 資料庫抓取資料 ******/
			sqlQuery = "SELECT * FROM `nourishment` WHERE company =" + "'"
					+ company + "'&& productname ='" + productname + "'";
			String getdata = "";
			try {
				rss = db.ExcuteQuery(sqlQuery);
				while (rss.next()) {
					ResultSetMetaData rsmd = rss.getMetaData();

					for (int i = 2; i <= rsmd.getColumnCount(); i++) {
						if (i == rsmd.getColumnCount())
							getdata = getdata + rss.getString(rsmd.getColumnName(i));
						else
							getdata = getdata+ rss.getString(rsmd.getColumnName(i)) + "、";
					}
				}
				
				db.CloseConn();
				out.print(getdata);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			break;

		case "insertdatabase":
			int primarykey = 0;
			String insertsqlQuery = "INSERT INTO `nourishment`";
			String column_name = "(";
			String insertdata = "VALUES(";

			/***** 取得網頁新增資料 *****/
			company = request.getParameter("company");
			productname = request.getParameter("productname");
			powder = request.getParameter("powder");
			water = request.getParameter("water");
			kcal = request.getParameter("kcal");
			protein = request.getParameter("protein");
			fat = request.getParameter("fat");
			carbohydrate = request.getParameter("carbohydrate");
			fiber = request.getParameter("fiber");
			ca = request.getParameter("ca");
			p = request.getParameter("p");
			na = request.getParameter("na");
			k = request.getParameter("k");
			mg = request.getParameter("mg");
			g_spoon = request.getParameter("g_spoon");

			try {
				/****** 資料庫是否存在 ******/
				sqlQuery = "SELECT * FROM `nourishment` WHERE company =" + "'"
						+ company + "'&& productname ='" + productname + "'";
				rss = db.ExcuteQuery(sqlQuery);
				rss.next();
				if (rss.getRow() == 0) {
					/****** 資料庫總欄位 ******/
					sqlQuery = "SELECT * FROM `nourishment`";
					rss = db.ExcuteQuery(sqlQuery);
					ResultSetMetaData rsmd = rss.getMetaData();
					for (int i = 1; i <= rsmd.getColumnCount(); i++) {
						if (i == rsmd.getColumnCount())
							column_name = column_name + rsmd.getColumnName(i) + ")";
						else
							column_name = column_name + rsmd.getColumnName(i) + ",";
					}

					/***** 取得primarykey *****/
					rss = db.ExcuteQuery("SELECT MAX(primarykey) FROM `nourishment`");
					while (rss.next()) {
						primarykey = Integer.parseInt(rss.getString(1)) + 1;
					}

					insertdata = insertdata + "'" + primarykey + "'," + "'"
							+ company + "'," + "'" + productname + "'," + "'"
							+ powder + "'," + "'" + water + "'," + "'" + kcal
							+ "'," + "'" + protein + "'," + "'" + fat + "',"
							+ "'" + carbohydrate + "'," + "'" + fiber + "',"
							+ "'" + ca + "'," + "'" + p + "'," + "'" + na
							+ "'," + "'" + k + "'," + "'" + mg + "'," + "'"
							+ g_spoon + "')";

					insertsqlQuery = insertsqlQuery + column_name + insertdata;
					System.out.print(insertsqlQuery + "\n");

					/***** 新增資料 *****/
					db.ExcuteUpdate(insertsqlQuery);
					out.print("Sucess InsertDatabase");
				} else 
					out.print("Database已存在");
				
				db.CloseConn();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			break;

		case "modifieddatabase":
			/***** 取得網頁新增資料 *****/
			company = request.getParameter("company");
			productname = request.getParameter("productname");
			powder = request.getParameter("powder");
			water = request.getParameter("water");
			kcal = request.getParameter("kcal");
			protein = request.getParameter("protein");
			fat = request.getParameter("fat");
			carbohydrate = request.getParameter("carbohydrate");
			fiber = request.getParameter("fiber");
			ca = request.getParameter("ca");
			p = request.getParameter("p");
			na = request.getParameter("na");
			k = request.getParameter("k");
			mg = request.getParameter("mg");
			g_spoon = request.getParameter("g_spoon");

			sqlQuery = "UPDATE `nourishment` SET " + "powder='" + powder
					+ "',water='" + water + "',kcal='" + kcal + "',protein='"
					+ protein + "',fat='" + fat + "',carbohydrate='"
					+ carbohydrate + "',fiber='" + fiber + "',ca='" + ca
					+ "',p='" + p + "',na='" + na + "',k='" + k + "',mg='" + mg
					+ "',g_spoon='" + g_spoon + "'" + "WHERE company =" + "'"
					+ company + "'&& productname ='" + productname + "'";
			System.out.print(sqlQuery + "\n");
			try {
				/***** 修改資料 *****/
				db.ExcuteUpdate(sqlQuery);
				out.print("Database已修改");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			break;

		case "deletdatabase":
			sqlQuery = "DELETE FROM `nourishment` WHERE company =" + "'"
					+ company + "'&& productname ='" + productname + "'";

			System.out.print(sqlQuery + "\n");
			try {
				/***** 刪除資料 *****/
				db.ExcuteUpdate(sqlQuery);
				out.print("Database已刪除");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			break;

		case "addlist":
			sqlQuery = "SELECT * FROM `nourishment` WHERE company =" + "'"
					+ company + "'&& productname ='" + productname + "'";

			try {
				float nourish_quantity = Float.parseFloat(request.getParameter("nourish_quantity"));
				float nourish_meal = Float.parseFloat(request.getParameter("nourish_meal"));
				float quantity = nourish_quantity * nourish_meal;
				
				rss = db.ExcuteQuery(sqlQuery) ;
				while (rss.next()) {
					quantity = quantity * rss.getFloat("g_spoon");
					float pro_nourish = Math.round(rss.getFloat("protein") / rss.getFloat("powder") * quantity);
					float fat_nourish = Math.round(rss.getFloat("fat") / rss.getFloat("powder")* quantity);
					float cho_nourish = Math.round(rss.getFloat("carbohydrate") / rss.getFloat("powder") * quantity);
					float cal_nourish = Math.round(pro_nourish * 4 + fat_nourish * 9 + cho_nourish * 4);
					
					NumberFormat nf = NumberFormat.getInstance();
					nf.setMaximumFractionDigits(0);

					out.print(company + "," + productname + ", 蛋白質：" + nf.format(pro_nourish)  
							+ "g, 脂肪："+ nf.format(fat_nourish) + "g, 醣類："+ nf.format(cho_nourish) 
							+ "g, 熱量："+ nf.format(cal_nourish) + "大卡");
				}
				
				db.CloseConn();
			} catch (SQLException e1) {
				// TODO: handle exception
				e1.printStackTrace();
			} catch (NumberFormatException e2) {
				// TODO: handle exception
				out.print("營養品填寫錯誤");
			}

			break;
		}

		out.close();
	}
}
